package com.qtummatrix.dao.daoImpl;

import com.mysql.cj.util.StringUtils;
import com.qtummatrix.dao.UserDao;
import com.qtummatrix.entity.*;
import com.qtummatrix.utils.DBUtil;
import com.qtummatrix.utils.TimeUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.LocalDate;
import java.util.*;

/**
 * @author 贾双龙
 * @date 2019/10/22 - 10:27
 */

public class UserDaoImpl implements UserDao {
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    Statement statement = null;
    ResultSet resultSet = null;

    LinkedList<Map> userList = new LinkedList<>();

    /**
     * 登录验证
     *
     * @param phone    手机号
     * @param password 用户密码
     * @return 返回值为user对象
     */
    @Override
    public User validateLogin(long phone, String password) {
        User user = null;
        try {
            //创建数据库连接
            connection = DBUtil.getConnection();
            String sql = "select id,headimg,username from user where phone = ? and password = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setLong(1, phone);
            preparedStatement.setString(2, password);
            //执行sql
            resultSet = preparedStatement.executeQuery();
            //获取执行后的结果
            if (resultSet.next()) {
                //声明user对象存放当前登录的用户id，头像和用户名
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setHeadimg(resultSet.getString("headimg"));
                user.setUsername(resultSet.getString("username"));
                //登陆成功添加用户 -- 将记录保存到login表中
                if (user != null) {
                    connection = DBUtil.getConnection();
                    String sql2 = "insert into login value(null,?,now())";
                    preparedStatement = connection.prepareStatement(sql2);
                    preparedStatement.setInt(1, user.getId());
                    preparedStatement.executeUpdate();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return user;
    }

    /**
     * 修改密码
     *
     * @param id
     * @param oldPassword 原密码
     * @param newPassword 新密码
     * @return 返回值为boolean值，判断密码是否修改成功
     */
    @Override
    public boolean changePassword(int id, String oldPassword, String newPassword) {
        String sql1 = "select password from user where id=?";
        String sql2 = "update user set password=? where id=?";
        boolean b = false;
        String beforPassword = "";

        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                beforPassword = resultSet.getString("password");
            }
            if (beforPassword.equals(oldPassword)) {
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setString(1, newPassword);
                preparedStatement.setInt(2, id);
                preparedStatement.executeUpdate();
                b = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }

        return b;
    }

    /**
     * 最近员工信息
     *
     * @return 返回值为list集合，list集合中存放的是最近的员工信息
     */
    @Override
    public List<User> recentInfo() {
        List<User> list = new ArrayList<>();
        String sql = "select * from user order by entryTime desc limit 6;";

        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery(sql);
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setPassword(resultSet.getString("password"));
                user.setUsername(resultSet.getString("username"));
                user.setAddress(resultSet.getString("address"));
                user.setDepartmentId(resultSet.getInt("departmentId"));
                user.setEmail(resultSet.getString("email"));
                user.setEntryTime(resultSet.getString("entryTime"));
                user.setHeadimg(resultSet.getString("headimg"));
                user.setSex(resultSet.getString("sex"));
                user.setPhone(resultSet.getLong("phone"));
                user.setQq(resultSet.getLong("qq"));
                user.setUID(resultSet.getLong("UID"));
                user.setPositionId(resultSet.getInt("positionId"));
                user.setMark(resultSet.getString("mark"));
                list.add(user);
            }
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }

        return list;
    }

    /**
     * 最近员工变动信息
     *
     * @return
     */
    @Override
    public List<ChangeInfo> recentChangeInfo() {
        List<ChangeInfo> list = new ArrayList<>();
        String sql1 = "select username,headimg,changetime,type,olddempartmentName,oldpositionName,\n" +
                "newdempartmentName,newpositionName\n" +
                "from  changetype,user,changeinfo,(select changeinfo.id as oldid,dempartmentName as " +
                "olddempartmentName,positionName as\n" +
                "oldpositionName from position,dempartment,changeinfo where\n" +
                " dempartment.id=position.departmentId and position.id=changeinfo.sPositionId) as a,(select " +
                "changeinfo.id as newid,dempartmentName as newdempartmentName,positionName as\n" +
                "newpositionName from position,dempartment,changeinfo where\n" +
                " dempartment.id=position.departmentId and position.id=changeinfo.ePositionId) as b where user" +
                ".id=changeinfo.userid\n" +
                "and changeinfo.typeid=changetype.id and a.oldid=b.newid and a.oldid=changeinfo.id order by " +
                "changetime desc limit 3";
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql1);
            resultSet = preparedStatement.executeQuery(sql1);
            while (resultSet.next()) {
                String username = resultSet.getString("username");
                String headimg = resultSet.getString("headimg");
                String changetime = resultSet.getString("changetime");
                String type = resultSet.getString("type");
                String info = "";
                if (type.equals("退休") || type.equals("离职")) {
                    info = "由" + resultSet.getString("olddempartmentName") +
                            resultSet.getString("oldpositionName") + type;
                } else {
                    info = "由" + resultSet.getString("olddempartmentName") +
                            resultSet.getString("oldpositionName") + type +
                            resultSet.getString("newdempartmentName") +
                            resultSet.getString("newpositionName");
                }
                ChangeInfo changeInfo = new ChangeInfo();
                changeInfo.setUsername(username);
                changeInfo.setInfo(info);
                changeInfo.setHeadimg(headimg);
                changeInfo.setChangetime(changetime);
                list.add(changeInfo);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 条件查询用户信息
     *
     * @param operationId 当前正在操作的用户id
     * @param username    用户名
     * @param department  部门名
     * @param starttime   查询的开始入职时间
     * @param endtime     查询的结束入职时间
     * @return 返回值为linkedlist集合，里面存放的是查询出来的用户信息
     */
    @Override
    public LinkedList<Map> search(int operationId, String username, String department, String starttime,
                                  String endtime) {
        LinkedList<Map> userList = new LinkedList<>();
        try {
            //根据对应的条件进行条件查询
            StringBuffer sql = new StringBuffer(
                    "select u.headimg, u.username, u.sex, d.dempartmentName, p.positionName, u.id from user as u " +
                            "left join dempartment as d on u.departmentId = d.id left join position as p on u" +
                            ".positionId = p.id where 1=1 ");
            //将查询的四个条件进行拆分，当该查询条件不为""时，将该条件缀在sql语句后
            String str1 = " and username = ? ";
            String str2 = " and dempartmentName = ? ";
            String str3 = " and entryTime > ? ";
            String str4 = " and entryTime < ? ";

            if (!StringUtils.isNullOrEmpty(username)) {
                sql.append(str1);
            }
            if (!StringUtils.isNullOrEmpty(department)) {
                sql.append(str2);
            }
            if (!StringUtils.isNullOrEmpty(starttime)) {
                sql.append(str3);
            }
            if (!StringUtils.isNullOrEmpty(endtime)) {
                sql.append(str4);
            }
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql.toString());
            //a 记录当前是第几个条件
            int a = 1;
            //为sql语句中的对应位置设置值
            if (!StringUtils.isNullOrEmpty(username)) {
                preparedStatement.setString(a, username);
                a++;
            }
            if (!StringUtils.isNullOrEmpty(department)) {
                preparedStatement.setString(a, department);
                a++;
            }
            if (!StringUtils.isNullOrEmpty(starttime)) {
                preparedStatement.setString(a, starttime);
                a++;
            }
            if (!StringUtils.isNullOrEmpty(endtime)) {
                preparedStatement.setString(a, endtime);
                a++;
            }
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                String headimg = resultSet.getString("u.headimg");
                String username1 = resultSet.getString("u.username");
                String sex = resultSet.getString("u.sex");
                String departmentName = resultSet.getString("d.dempartmentName");
                String positionName = resultSet.getString("p.positionName");
                int id = resultSet.getInt("u.id");
                Map map = new HashMap();
                //将查询的信息存放在map中 key-value
                map.put("headimg", headimg);
                map.put("username", username1);
                map.put("sex", sex);
                map.put("departmentName", departmentName);
                map.put("positionName", positionName);
                map.put("id", id);
                //将查询出来的所有用户的map信息集合加到list中
                userList.add(map);
            }
            return userList;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 出勤率
     *
     * @param time
     * @param begin
     * @param stop
     * @return
     */
    @Override
    public Pie attendanceRate(String time, String begin, String stop) {
        int times = 0;
        Pie pie = new Pie();
        String lasttime = "";
        if (begin != "" && stop != "") {
            Date begindate = TimeUtil.stringToDate(begin);
            Date stopdate = TimeUtil.stringToDate(stop);
            LocalDate localbegin = TimeUtil.dateToLocalDate(begindate);
            LocalDate localstop = TimeUtil.dateToLocalDate(stopdate);
            times = (int) (localstop.toEpochDay() - localbegin.toEpochDay());
            lasttime = stop;
        } else {
            if (time.equals("最近三天")) {
                times = 2;
            }
            if (time.equals("最近一周")) {
                times = 6;
            }
            if (time.equals("最近一个月")) {
                times = 29;
            }
            lasttime = TimeUtil.getNowDate();
        }

        try {
            connection = DBUtil.getConnection();
            String sql = "select starttime,endtime from attendance where datediff(?,endtime)<=? and leaveclass ='请假' " +
                    "or '出差' or '旷工';";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, lasttime);
            preparedStatement.setInt(2, times);
            resultSet = preparedStatement.executeQuery();
            int sum = 0; //未出勤人次
            while (resultSet.next()) {
                Date starttime = resultSet.getDate("starttime");
                Date endtime = resultSet.getDate("endtime");
                LocalDate start = TimeUtil.dateToLocalDate(starttime);
                LocalDate end = TimeUtil.dateToLocalDate(endtime);
                LocalDate now = LocalDate.now();
                if (now.toEpochDay() - start.toEpochDay() > times) {
                    sum += times - (now.toEpochDay() - end.toEpochDay());
                } else {
                    if (start.toEpochDay() == end.toEpochDay()) {
                        sum += 1;
                    } else {
                        sum += end.toEpochDay() - start.toEpochDay();
                    }
                }
            }
            System.out.println("sum" + sum);
            String sql2 = "select count(id) from user;";
            preparedStatement = connection.prepareStatement(sql2);
            resultSet = preparedStatement.executeQuery(sql2);
            int sumUser = 0;
            while (resultSet.next()) {
                sumUser = resultSet.getInt("count(id)") * times;
            }
            int attendance = sumUser - sum;
            String type = "出勤率";

            pie.setRateType(type);
            pie.setValue1(attendance);
            pie.setValue2(sum);
            System.out.println(pie);
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
            DBUtil.close(null, preparedStatement, null);
        }
        return pie;
    }

    /**
     * 辞职率
     *
     * @param time
     * @param begin
     * @param stop
     * @return
     */
    @Override
    public Pie vacateRate(String time, String begin, String stop) {
        int times = 0;
        Pie pie = new Pie();
        String lasttime = "";
        if (begin != "" && stop != "") {
            Date begindate = TimeUtil.stringToDate(begin);
            Date stopdate = TimeUtil.stringToDate(stop);
            LocalDate localbegin = TimeUtil.dateToLocalDate(begindate);
            LocalDate localstop = TimeUtil.dateToLocalDate(stopdate);
            times = (int) (localstop.toEpochDay() - localbegin.toEpochDay());
            lasttime = stop;
        } else {
            if (time.equals("最近三天")) {
                times = 2;
            }
            if (time.equals("最近一周")) {
                times = 6;
            }
            if (time.equals("最近一个月")) {
                times = 29;
            }
            lasttime = TimeUtil.getNowDate();
        }

        try {
            connection = DBUtil.getConnection();
            String sql = "select starttime,endtime from attendance where datediff(?,endtime)<=? and leaveclass ='请假' " +
                    "or '出差' or '旷工';";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, lasttime);
            preparedStatement.setInt(2, times);
            resultSet = preparedStatement.executeQuery();
            int sum = 0; //请假人次
            while (resultSet.next()) {
                Date starttime = resultSet.getDate("starttime");
                Date endtime = resultSet.getDate("endtime");
                LocalDate start = TimeUtil.dateToLocalDate(starttime);
                LocalDate end = TimeUtil.dateToLocalDate(endtime);
                LocalDate now = LocalDate.now();
                if (now.toEpochDay() - start.toEpochDay() > times) {
                    sum += times - (now.toEpochDay() - end.toEpochDay());
                } else {
                    if (start.toEpochDay() == end.toEpochDay()) {
                        sum += 1;
                    } else {
                        sum += end.toEpochDay() - start.toEpochDay();
                    }
                }
            }
            String sql2 = "select count(id) from user;";
            preparedStatement = connection.prepareStatement(sql2);
            resultSet = preparedStatement.executeQuery(sql2);
            int sumUser = 0;
            while (resultSet.next()) {
                sumUser = resultSet.getInt("count(id)") * times;
            }
            int other = sumUser - sum;
            String type = "请假率";

            pie.setRateType(type);
            pie.setValue1(sum);
            pie.setValue2(other);
            System.out.println(pie);
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return pie;
    }

    /**
     * 出差率
     *
     * @param time
     * @param begin
     * @param stop
     * @return
     */
    @Override
    public Pie travelRate(String time, String begin, String stop) {
        int times = 0;
        Pie pie = new Pie();
        String lasttime = "";
        if (begin != "" && stop != "") {
            Date begindate = TimeUtil.stringToDate(begin);
            Date stopdate = TimeUtil.stringToDate(stop);
            LocalDate localbegin = TimeUtil.dateToLocalDate(begindate);
            LocalDate localstop = TimeUtil.dateToLocalDate(stopdate);
            times = (int) (localstop.toEpochDay() - localbegin.toEpochDay());
            lasttime = stop;
        } else {
            if (time.equals("最近三天")) {
                times = 2;
            }
            if (time.equals("最近一周")) {
                times = 6;
            }
            if (time.equals("最近一个月")) {
                times = 29;
            }
            lasttime = TimeUtil.getNowDate();
        }

        try {
            connection = DBUtil.getConnection();
            String sql = "select starttime,endtime from attendance where datediff(?,endtime)<=? and leaveclass ='请假' " +
                    "or '出差' or '旷工';";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, lasttime);
            preparedStatement.setInt(2, times);
            resultSet = preparedStatement.executeQuery();
            int sum = 0;//出差人次
            while (resultSet.next()) {
                Date starttime = resultSet.getDate("starttime");
                Date endtime = resultSet.getDate("endtime");
                LocalDate start = TimeUtil.dateToLocalDate(starttime);
                LocalDate end = TimeUtil.dateToLocalDate(endtime);
                LocalDate now = LocalDate.now();
                if (now.toEpochDay() - start.toEpochDay() > times) {
                    sum += times - (now.toEpochDay() - end.toEpochDay());
                } else {
                    if (start.toEpochDay() == end.toEpochDay()) {
                        sum += 1;
                    } else {
                        sum += end.toEpochDay() - start.toEpochDay();
                    }
                }
            }
            String sql2 = "select count(id) from user;";
            preparedStatement = connection.prepareStatement(sql2);
            resultSet = preparedStatement.executeQuery(sql2);
            int sumUser = 0;
            while (resultSet.next()) {
                sumUser = resultSet.getInt("count(id)") * times;
            }
            int other = sumUser - sum;
            String type = "出差率";

            pie.setRateType(type);
            pie.setValue1(sum);
            pie.setValue2(other);
            System.out.println(pie);
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return pie;
    }

    /**
     * 迟到率
     *
     * @param time
     * @param begin
     * @param stop
     * @return
     */
    @Override
    public Pie lateRate(String time, String begin, String stop) {
        int times = 0;
        Pie pie = new Pie();
        String lasttime = "";
        if (begin != "" && stop != "") {
            Date begindate = TimeUtil.stringToDate(begin);
            Date stopdate = TimeUtil.stringToDate(stop);
            LocalDate localbegin = TimeUtil.dateToLocalDate(begindate);
            LocalDate localstop = TimeUtil.dateToLocalDate(stopdate);
            times = (int) (localstop.toEpochDay() - localbegin.toEpochDay());
            lasttime = stop;
        } else {
            if (time.equals("最近三天")) {
                times = 2;
            }
            if (time.equals("最近一周")) {
                times = 6;
            }
            if (time.equals("最近一个月")) {
                times = 29;
            }
            lasttime = TimeUtil.getNowDate();
        }

        try {
            connection = DBUtil.getConnection();
            String sql = "select starttime,endtime from attendance where datediff(?,endtime)<=? and leaveclass ='请假' " +
                    "or '出差' or '旷工';";

            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, lasttime);
            preparedStatement.setInt(2, times);
            resultSet = preparedStatement.executeQuery();
            int sum = 0; //迟到/早退人次
            while (resultSet.next()) {
                Date starttime = resultSet.getDate("starttime");
                Date endtime = resultSet.getDate("endtime");
                LocalDate start = TimeUtil.dateToLocalDate(starttime);
                LocalDate end = TimeUtil.dateToLocalDate(endtime);
                LocalDate now = LocalDate.now();
                if (now.toEpochDay() - start.toEpochDay() > times) {
                    sum += times - (now.toEpochDay() - end.toEpochDay());
                } else {
                    if (start.toEpochDay() == end.toEpochDay()) {
                        sum += 1;
                    } else {
                        sum += end.toEpochDay() - start.toEpochDay();
                    }
                }
            }
            String sql2 = "select count(id) from user;";
            preparedStatement = connection.prepareStatement(sql2);
            resultSet = preparedStatement.executeQuery(sql2);
            int sumUser = 0;
            while (resultSet.next()) {
                sumUser = resultSet.getInt("count(id)") * times;
            }
            int other = sumUser - sum;
            String type = "迟到早退率";

            pie.setRateType(type);
            pie.setValue1(sum);
            pie.setValue2(other);
            System.out.println(pie);
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return pie;
    }

    /**
     * 部门类型招人情况
     *
     * @param departmentType
     * @return
     */
    @Override
    public RecruitInfo hiringSituation(String departmentType) {
        String sql = " select value1,value2 from (select sum(position.planNumber) as value1 from position where " +
                "departmentId=(select id from dempartment\n" +
                " where dempartmentName=?)) as a ,(select count(user.id) as value2 from user where user.departmentId=" +
                "(select id from dempartment\n" +
                " where dempartmentName=?)) as b;";
        int value1 = 0;
        int value2 = 0;
        try {
            connection = DBUtil.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, departmentType);
            preparedStatement.setString(2, departmentType);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                value1 = resultSet.getInt("value1");
                value2 = resultSet.getInt("value2");
            }
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        RecruitInfo recruitInfo = new RecruitInfo();
        recruitInfo.setDepartmentName(departmentType);
        recruitInfo.setValue1(value1);
        recruitInfo.setValue2(value2);
        return recruitInfo;
    }

    /**
     * 操作信息
     *
     * @return
     */
    public List<Operation> operationInfo() {
        String sql = "select userid,adminid,operationType.type,operationTime\n" +
                " from operation,operationType where operation.adminid in\n" +
                "(select adminid from (select adminid from operation group by adminid\n" +
                "order by max(operationTime) desc limit 1) as a) and\n" +
                " operation.type=operationType.id order by operationTime desc limit 2";

        String sql4 = "select userid,adminid,operationType.type,operationTime\n" +
                "                from operation,operationType where operation.adminid in\n" +
                "                (select adminid from (select adminid from operation group by adminid\n" +
                "               order by max(operationTime) desc limit 1,1) as a) and\n" +
                "              operation.type=operationType.id ORDER BY operationTime desc LIMIT 2";

        String sql2 = "select headimg,logintime from user,login where user.id=login.userid and\n" +
                " user.id=? order by logintime desc limit 1;";
        String sql3 = "select username from user where id=?";
        ResultSet resultSet1 = null;
        int userid = 0;
        int adminid = 0;
        String headimg = "";
        String logintime = "";
        String username = "";
        String operationType = "";
        String operationTime = "";
        Operation operation1 = new Operation();
        Operation operation2 = new Operation();
        int index = 0;
        try {
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql);
            while (resultSet.next()) {
                userid = resultSet.getInt("userid");
                adminid = resultSet.getInt("adminid");
                operationType = resultSet.getString("operationType.type");
                operationTime = resultSet.getString("operationTime");
                operationTime = operationTime.substring(0, operationTime.lastIndexOf("-") + 3);
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setInt(1, adminid);
                resultSet1 = preparedStatement.executeQuery();
                while (resultSet1.next()) {
                    headimg = resultSet1.getString("headimg");
                    logintime = resultSet1.getString("logintime");
                    logintime = logintime.substring(0, logintime.lastIndexOf("-") + 3);
                }
                preparedStatement = connection.prepareStatement(sql3);
                preparedStatement.setInt(1, userid);
                resultSet1 = preparedStatement.executeQuery();
                while (resultSet1.next()) {
                    username = resultSet1.getString("username");
                }
                if (index > 0) {
                    operation1.setOperationinfo2(operationType + "<span style='color:orange;'>" + username + "</span" +
                            ">" + "员工信息" + operationTime);

                } else {
                    operation1.setHeadimg(headimg);
                    operation1.setLogintime(logintime);
                    operation1.setOperationinfo1(operationType + "<span style='color:orange;'>" + username + "</span" +
                            ">" + "员工信息" + operationTime);
                    index++;
                }

            }
            resultSet = statement.executeQuery(sql4);
            while (resultSet.next()) {
                userid = resultSet.getInt("userid");
                adminid = resultSet.getInt("adminid");
                operationType = resultSet.getString("operationType.type");
                operationTime = resultSet.getString("operationTime");
                operationTime = operationTime.substring(0, operationTime.lastIndexOf("-") + 3);
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setInt(1, adminid);
                resultSet1 = preparedStatement.executeQuery();
                while (resultSet1.next()) {
                    headimg = resultSet1.getString("headimg");
                    logintime = resultSet1.getString("logintime");
                    logintime = logintime.substring(0, logintime.lastIndexOf("-") + 3);
                }
                preparedStatement = connection.prepareStatement(sql3);
                preparedStatement.setInt(1, userid);
                resultSet1 = preparedStatement.executeQuery();
                while (resultSet1.next()) {
                    username = resultSet1.getString("username");
                }
                if (index > 1) {
                    operation2.setOperationinfo2(operationType + "<span style='color:orange;'>" + username + "</span" +
                            ">" + "员工信息" + operationTime);

                } else {
                    operation2.setHeadimg(headimg);
                    operation2.setLogintime(logintime);
                    operation2.setOperationinfo1(operationType + "<span style='color:orange;'>" + username + "</span" +
                            ">" + "员工信息" + operationTime);
                    index++;
                }
            }
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
            DBUtil.close(null, statement, null);
        }
        List<Operation> list = new ArrayList<>();
        list.add(operation1);
        list.add(operation2);

        return list;
    }

    public List<Integer> examine() {
        List<Integer> list = new ArrayList<>();
        String sql1 = "SELECT count(*)num1 FROM attendance where \n " +
                "aduitState='审核通过' GROUP BY aduitState";
        String sql2 = "SELECT count(*) num2 FROM \n" +
                "attendance where aduitState='审核未通过' GROUP BY aduitState";
        String sql3 = "SELECT count(*) num3 FROM attendance where aduitState='未审核' GROUP BY aduitState";

        int num1 = 0;
        int num2 = 0;
        int num3 = 0;
        try {
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql1);
            if (resultSet.next()) {
                num1 = resultSet.getInt("num1");
            }
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql2);
            if (resultSet.next()) {
                num2 = resultSet.getInt("num2");
            }
            connection = DBUtil.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery(sql3);
            if (resultSet.next()) {
                num3 = resultSet.getInt("num3");
            }
            list.add(num1);
            list.add(num2);
            list.add(num3);
        } catch (Exception e) {

        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return list;
    }


    /**
     * 查询用户信息
     *
     * @param operationId 当前操作的用户id
     * @param x
     * @return
     */
    @Override
    public LinkedList<Map> selectUserInfo(int operationId, int x) {
        try {
            //创建数据库连接
            connection = DBUtil.getConnection();
            String sql = "SELECT u.headimg, u.username, u.sex, d.dempartmentName, p.positionName, u.id FROM user as " +
                    "u left join dempartment as d on u.departmentId=d.id\n" +
                    "left join position as p on u.positionId = p.id limit ?, 6";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, x);
            resultSet = preparedStatement.executeQuery();
            Map map = null;
            while (resultSet.next()) {
                String headimg = resultSet.getString("u.headimg");
                String username = resultSet.getString("u.username");
                String sex = resultSet.getString("u.sex");
                String departmentName = resultSet.getString("d.dempartmentName");
                String positionName = resultSet.getString("p.positionName");
                int id = resultSet.getInt("u.id");
                map = new HashMap();
                map.put("headimg", headimg);
                map.put("username", username);
                map.put("sex", sex);
                map.put("departmentName", departmentName);
                map.put("positionName", positionName);
                map.put("id", id);
                userList.add(map);
            }
            /*String sql1 = "insert into operation values(null, ?, 4, now(),?)";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, operationId);
            preparedStatement.setInt(2, operationId);
            int i = preparedStatement.executeUpdate();*/
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return userList;
    }

    /**
     * 添加用户 向用户表中添加信息
     *
     * @param operationId 当前操作的用户ID
     * @param user        要添加的user对象
     */
    @Override
    public int addUser(int operationId, User user) {
        try {
            connection = DBUtil.getConnection();
            //首先判断用户的手机号码是否存在
            String sql1 = "select id from user where phone = ?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setLong(1, user.getPhone());
            resultSet = preparedStatement.executeQuery();
            int id = 0;
            if (resultSet.next()) {
                id = resultSet.getInt("id");
            }
            if (id == 0) {
                //如果id==0表示查询的phone不存在，表示1可以添加新员工
                String sql = "insert into user(password,sex,username,phone,qq,UID,email,address,departmentId," +
                        "positionId,entryTime,IsAdmin) values (?,?,?,?,?,?,?,?,?,?,NOW(),?)";
                preparedStatement = connection.prepareStatement(sql);
                //1密码
                preparedStatement.setString(1, user.getPassword());
                //2性别
                preparedStatement.setString(2, user.getSex());
                //3姓名
                preparedStatement.setString(3, user.getUsername());
                //4电话号码
                preparedStatement.setLong(4, user.getPhone());
                //5qq
                preparedStatement.setLong(5, user.getQq());
                //6身份号
                preparedStatement.setLong(6, user.getUID());
                //7email
                preparedStatement.setString(7, user.getEmail());
                //8住址
                preparedStatement.setString(8, user.getAddress());
                //9部门id
                preparedStatement.setInt(9, user.getDepartmentId());
                //10职位id
                preparedStatement.setInt(10, user.getPositionId());
                String isAdmin = user.getIsAdmin();
                //11权限
                int admin = 0;
                if (isAdmin.equals("管理员")) {
                    admin = 1;
                }
                preparedStatement.setInt(11, admin);
                int i = preparedStatement.executeUpdate();
                //3、最后将添加员工信息的记录存入到operation表中
                String sql3 = "insert into operation values(null, ?, 1, now(),?)";
                preparedStatement = connection.prepareStatement(sql3);
                preparedStatement.setInt(1, user.getId());
                preparedStatement.setInt(2, operationId);
                //i1表示用户操作记录是否添加成功
                preparedStatement.executeUpdate();
                //i 表示是否插入成功
                return i;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return 0;
    }

    /**
     * 删除用户
     *
     * @param operationId 正在操作的用户id
     * @param id          被删除的用户id
     * @return 返回值为是否删除成功
     */
    @Override
    public int delUser(int operationId, int id) {
        try {
            connection = DBUtil.getConnection();
            String sql = "delete from user where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            int i = preparedStatement.executeUpdate();
            //添加员工删除记录
            String sql1 = "insert into operation values(null,?,2,now(),?)";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, id);
            preparedStatement.setInt(2, operationId);
            preparedStatement.executeUpdate();
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, null);
        }
        return 0;
    }


    @Override
    public List<User> showUserByDepartment(String department) {
        return null;
    }

    @Override
    public List<User> showUserByWork(String workName) {
        return null;
    }

    /**
     * 根据id查看员工信息
     *
     * @param operationId 当前登录的员工Id
     * @param id          被查看的员工Id
     * @return
     */
    @Override
    public LinkedList<Map> showUserInfo(int operationId, int id) {
        try {
            connection = DBUtil.getConnection();
            String sql = "select u.headimg,u.username,u.sex, u.phone,u.email,u.qq,u.UID,u.address,u.entryTime,d" +
                    ".dempartmentName,p.positionName from user as u left join dempartment as d on u.departmentId = d" +
                    ".id left join position as p on u.positionId = p.id where u.id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            String headimg = "";
            String username1 = "";
            String sex = "";
            long phone = 0L;
            String email = "";
            long qq = 0L;
            long uid = 0L;
            String address = "";
            String entrytime = "";
            String departmentName = "";
            String positionName = "";


            if (resultSet.next()) {
                //获取数据库查询的员工的信息
                headimg = resultSet.getString("u.headimg");
                username1 = resultSet.getString("u.username");
                sex = resultSet.getString("u.sex");
                phone = resultSet.getLong("u.phone");
                email = resultSet.getString("u.email");
                qq = resultSet.getLong("u.qq");
                uid = resultSet.getLong("u.uid");
                address = resultSet.getString("u.address");
                entrytime = String.valueOf(resultSet.getDate("u.entrytime"));
                departmentName = resultSet.getString("d.dempartmentName");
                positionName = resultSet.getString("p.positionName");
            }
            //将该员工的信息，添加到map集合中
            Map map = new HashMap();
            map.put("headimg", headimg);
            map.put("username", username1);
            map.put("sex", sex);
            map.put("departmentName", departmentName);
            map.put("positionName", positionName);
            map.put("phone", phone);
            map.put("qq", qq);
            map.put("uid", uid);
            map.put("email", email);
            map.put("address", address);
            map.put("entrytime", entrytime);
            map.put("id", id);

            connection = DBUtil.getConnection();
            String sql2 = "select changetime,olddempartmentName,oldpositionName,type,newdempartmentName," +
                    "newpositionName " +
                    "from  changetype,user,changeinfo,(select changeinfo.id as oldid,dempartmentName as " +
                    "olddempartmentName,positionName as oldpositionName " +
                    "from position,dempartment,changeinfo where dempartment.id=position.departmentId and position" +
                    ".id=changeinfo.sPositionId) as a," +
                    "(select changeinfo.id as newid,dempartmentName as newdempartmentName,positionName as " +
                    "newpositionName " +
                    "from position,dempartment,changeinfo where dempartment.id=position.departmentId and position" +
                    ".id=changeinfo.ePositionId) as b " +
                    "where user.id=changeinfo.userid  and changeinfo.typeid=changetype.id and a.oldid=b.newid and" +
                    " a" +
                    ".oldid=changeinfo.id and user.id = ? order by changetime desc limit 1";
            preparedStatement = connection.prepareStatement(sql2);
            preparedStatement.setInt(1, id);
            resultSet = preparedStatement.executeQuery();
            String changetime = "";
            String olddempartmentName = "";
            String oldpositionName = "";
            String type = "";
            String newdepartmentName = "";
            String newpositionName = "";
            if (resultSet.next()) {
                changetime = String.valueOf(resultSet.getDate("changetime"));
                olddempartmentName = resultSet.getString("olddempartmentName");
                oldpositionName = resultSet.getString("oldpositionName");
                type = resultSet.getString("type");
                newdepartmentName = resultSet.getString("newdempartmentName");
                newpositionName = resultSet.getString("newpositionName");
            }

            map.put("changetime", changetime);
            map.put("olddempartmentName", olddempartmentName);
            map.put("oldpositionName", oldpositionName);
            map.put("type", type);
            map.put("newdepartmentName", newdepartmentName);
            map.put("newpositionName", newpositionName);
            userList.add(map);
            //将该次查询的记录信息，插入到operation记录表中
            String sql1 = "insert into operation values(null, ?, 4, now(),?)";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setInt(1, id);
            preparedStatement.setInt(2, operationId);
            int i = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return userList;
    }

    @Override
    public List<User> showUserBySearch(String username, String departmentName, String beginTime, String overTime) {
        return null;
    }

    /**
     * 修改员工信息
     *
     * @param user
     * @return user 修改员工信息后显示用户修改后的信息
     */
    @Override
    public LinkedList<User> updateUser(int operationId, User user) {
        LinkedList<User> list1 = new LinkedList<User>();
        try {
            connection = DBUtil.getConnection();
            String sql1 = "select u.departmentId,u.positionId from user as u " +
                    "left join position as p on u.positionId = p.id " +
                    "left join dempartment as d on u.departmentId = d.id " +
                    "where u.id =?";
            preparedStatement = connection.prepareStatement(sql1);
            preparedStatement.setLong(1, user.getId());
            resultSet = preparedStatement.executeQuery();
            int deparementId = 0;
            int positionId = 0;
            if (resultSet.next()) {
                deparementId = resultSet.getInt("u.departmentId");
                positionId = resultSet.getInt("u.positionId");
            }
            user.setPositionId(positionId);
            user.setDepartmentId(deparementId);
            User user1 = updateUser1(operationId, user);
            list1.add(user1);
            return list1;

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return null;
    }

    /**
     * 更新员工信息
     *
     * @param operationId 更新员工信息的用户id
     * @param user        要更新的员工信息user对象
     * @return
     */
    public User updateUser1(int operationId, User user) {
//获取user对象存放的部门id
        int deparementId = user.getDepartmentId();
        //获取user对象存放的职位id
        int positionId = user.getPositionId();
        int id1 = 0;//id1存放的是部门id
        int id2 = 0;//id2存放的是职位id
        if (deparementId != 0 && positionId != 0) {
            try {
                connection = DBUtil.getConnection();
                //首先获取对应部门和对应职位相对应的id号
                String sql2 = "SELECT id  from dempartment where dempartmentName=?";
                String sql3 = "SELECT id  from position where positionName=?";
                preparedStatement = connection.prepareStatement(sql2);
                preparedStatement.setString(1, user.getDepartmentName());
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    id1 = resultSet.getInt("id");
                }
                //
                preparedStatement = connection.prepareStatement(sql3);
                preparedStatement.setString(1, user.getPositionName());
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    id2 = resultSet.getInt("id");
                }
                //执行更新员工信息的操作
                String sql = "UPDATE user SET username=?, headimg=?, sex=?, phone=?, email=?" +
                        ",qq=?, uid=?, address=?, departmentId=?, positionId=?, entrytime = ? where id = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, user.getUsername());
                preparedStatement.setString(2, user.getHeadimg());
                preparedStatement.setString(3, user.getSex());
                preparedStatement.setLong(4, user.getPhone());
                preparedStatement.setString(5, user.getEmail());
                preparedStatement.setLong(6, user.getQq());
                preparedStatement.setLong(7, user.getUID());
                preparedStatement.setString(8, user.getAddress());
                preparedStatement.setInt(9, id1);
                preparedStatement.setInt(10, id2);
                preparedStatement.setString(11, user.getEntryTime());
                preparedStatement.setLong(12, user.getId());
                int i = preparedStatement.executeUpdate();
                //将修改操作插入到operation表中
                String sql1 = "insert into operation values(null, ?, 3, now(),?)";
                preparedStatement = connection.prepareStatement(sql1);
                preparedStatement.setInt(1, user.getId());
                preparedStatement.setInt(2, operationId);
                preparedStatement.executeUpdate();
                //返回user对象将值填充到页面上
                return user;
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtil.close(connection, preparedStatement, resultSet);
            }
        }
        return null;
    }

    @Override
    public void addCheck(long phone, String checkType) {

    }

    @Override
    public void updateCheck() {

    }

    /**
     * 查询有数据库中又多少条员工信息记录
     *
     * @return 返回值为信息的条数
     */
    @Override
    public int selectTotalPerson() {
        int i = 0;
        try {
            connection = DBUtil.getConnection();
            String sql = "select count(id) from user";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                i = resultSet.getInt("count(id)");
            }
            return i;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return 0;
    }

    /**
     * 员工信息管理页面的分页  下一页
     *
     * @param operationId 执行操作的用户id
     * @param page        当前页码
     * @return 返回查询的下一页的所有员工信息
     */
    @Override
    public LinkedList<Map> getNextPage(int operationId, int page) {
        //查询出总共有多少条数据
        int totalPage = selectTotalPerson();
        //计算需要多少页显示，设置固定每页显示 6 条记录
        int pageNum = totalPage % 6 == 0 ? totalPage / 6 : totalPage / 6 + 1;
        if (page < pageNum) {
            page++;
            page = (page - 1) * 6;
            LinkedList<Map> maps = selectUserInfo(operationId, page);
            /*try {
                connection = DBUtil.getConnection();
                String sql = "insert into operation values(null, ?, 4, now(),?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, operationId);
                preparedStatement.setInt(2, operationId);
                int i = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtil.close(connection, preparedStatement, null);
            }*/
            return maps;
        } else {
            return null;
        }
    }

    /**
     * 员工信息管理页面的分页  上一页
     *
     * @param operationId 执行操作的用户id
     * @param page        当前页码
     * @return 返回查询的上一页的所有员工信息
     */
    public LinkedList<Map> getPrevious(int operationId, int page) {
        //查询出总共有多少条数据
        int totalPage = selectTotalPerson();
        //计算需要多少页显示，设置固定每页显示 6 条记录
        int pageNum = totalPage % 6 == 0 ? totalPage / 6 : totalPage / 6 + 1;
        if (page > 1) {
            page--;
            page = (page - 1) * 6;
            LinkedList<Map> maps = selectUserInfo(operationId, page);
            /*try {
                connection = DBUtil.getConnection();
                String sql = "insert into operation values(null, ?, 4, now(),?)";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setInt(1, operationId);
                preparedStatement.setInt(2, operationId);
                int i = preparedStatement.executeUpdate();
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                DBUtil.close(connection, preparedStatement, null);
            }*/
            return maps;
            //如果上一页为首页，则显示首页的内容
        } else if (page == pageNum) {
            return null;
        } else {
            return null;
        }
    }

    /**
     * 上传头像，更改图像路径
     *
     * @param id
     * @param headimgURL
     */
    @Override
    public void updateUserHeadimg(int id, String headimgURL) {
        try {
            connection = DBUtil.getConnection();
            String sql = "update user set headimg = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, headimgURL);
            preparedStatement.setInt(2, id);
            int i = preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, null);
        }
    }


    public List<ChangeUser> ChangeUser(int n) {
        n = n * 5;
        List<ChangeUser> list = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql = "select u.headimg,u.username,u.sex,d.dempartmentName,p.positionName,c.changetime from " +
                    "changeinfo c\n" +
                    "join user u on c.userid = u.id\n" +
                    "join dempartment d on d.id = u.departmentId\n" +
                    "join position p on p.id = c.ePositionId order by c.changetime desc limit ?,5";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, n);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ChangeUser user = new ChangeUser();
                user.setUsername(resultSet.getString("username"));
                user.setSex(resultSet.getString("sex"));
                user.setHeadimg(resultSet.getString("headimg"));
                user.setDempartmentName(resultSet.getString("dempartmentName"));
                user.setPositionName(resultSet.getString("positionName"));
                user.setChangetime(resultSet.getString("changetime"));
                list.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    public List<ChangeUser> showUser(String username, String changeInfo) {
        List<ChangeUser> list = new ArrayList<>();
        String sql = null;

        try {
            connection = DBUtil.getConnection();
            if (username != "" && changeInfo != "") {
                sql = "select u.headimg,u.username,u.sex,d.dempartmentName,p.positionName,c.changetime from " +
                        "changeinfo c\n" +
                        "join user u on c.userid = u.id\n" +
                        "join dempartment d on d.id = u.departmentId\n" +
                        "join position p on p.id = u.positionId\n" +
                        "where u.username = ? or d.dempartmentName = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, username);
                preparedStatement.setString(2, changeInfo);
                resultSet = preparedStatement.executeQuery();
            } else if (username != "" && changeInfo == "") {
                sql = "select u.headimg,u.username,u.sex,d.dempartmentName,p.positionName,c.changetime from " +
                        "changeinfo c\n" +
                        "join user u on c.userid = u.id\n" +
                        "join dempartment d on d.id = u.departmentId\n" +
                        "join position p on p.id = u.positionId\n" +
                        "where u.username = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, username);
                resultSet = preparedStatement.executeQuery();
            } else if (changeInfo != "" && username == "") {
                sql = "select u.headimg,u.username,u.sex,d.dempartmentName,p.positionName,c.changetime from " +
                        "changeinfo c\n" +
                        "join user u on c.userid = u.id\n" +
                        "join dempartment d on d.id = u.departmentId\n" +
                        "join position p on p.id = u.positionId\n" +
                        "where d.dempartmentName = ?";
                preparedStatement = connection.prepareStatement(sql);
                preparedStatement.setString(1, changeInfo);
                resultSet = preparedStatement.executeQuery();
            }
            while (resultSet.next()) {
                ChangeUser user = new ChangeUser();
                user.setUsername(resultSet.getString("username"));
                user.setSex(resultSet.getString("sex"));
                user.setHeadimg(resultSet.getString("headimg"));
                user.setDempartmentName(resultSet.getString("dempartmentName"));
                user.setPositionName(resultSet.getString("positionName"));
                user.setChangetime(resultSet.getString("changetime"));
                list.add(user);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
        return list;
    }

    /**
     * 编辑员工职位变动信息
     * username  员工
     * changeType 变动类型
     * newPosition 变动后职位
     * newType 部门
     */
    public void updateChangeUser(String username, String changeType, String newPosition, String newType) {
        String sql = null;
        //职位id
        int newPositionID = 0;
        //变动类型
        int changetypeID = 0;
        //变动后部门
        int newTypeId = 0;
        //员工姓名
        int userId = 0;
        int departId = 0;
        int positionId = 0;
        try {
            connection = DBUtil.getConnection();
            sql = "select id from position where positionName = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, newPosition);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                newPositionID = resultSet.getInt("id");
            }
            sql = "select id from changetype where type = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, changeType);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                changetypeID = resultSet.getInt("id");
            }
            sql = "select id from dempartment where dempartmentName = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, newType);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                newTypeId = resultSet.getInt("id");
            }
            sql = "select id,departmentId,positionId from user where username = ? ";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, username);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                userId = resultSet.getInt("id");
                departId = resultSet.getInt("departmentId");
                positionId = resultSet.getInt("positionId");
            }

            sql = "update user set positionId = ? ,departmentId = ? where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, newPositionID);
            preparedStatement.setInt(2, newTypeId);
            preparedStatement.setInt(3, userId);
            preparedStatement.executeUpdate();

            sql = "insert into changeinfo(userid,typeid,sPositionId,ePositionId,changetime) values" +
                    "(?,?,?,?,now())";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setInt(1, userId);
            preparedStatement.setInt(2, changetypeID);
            preparedStatement.setInt(3, positionId);
            preparedStatement.setInt(4, newPositionID);
            preparedStatement.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            DBUtil.close(connection, preparedStatement, resultSet);
        }
    }


}
